package com.szholly.pro.watergas_server.ps.android.controller;

import java.lang.management.BufferPoolMXBean;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.util.TwoTuple;
import com.szholly.pro.watergas_server.ps.android.bean.PageInfo;
import com.szholly.pro.watergas_server.ps.android.bean.PipeSearchParm;
import com.szholly.pro.watergas_server.ps.android.common.SqlUtils;
import com.szholly.pro.watergas_server.ps.android.common.ValidUtils;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;

/**
 * 排水管网搜索功能
 * 
 * 2017/5/17 <br/>
 *
 * @author Joey Huang QQ:273179370）
 */
public class PipeSearchService {
	public final static PipeSearchService INSTANCE = new PipeSearchService();
	private final static Map<String, Cache> CACHE_MAP = new HashMap<>();// 缓存搜索条目数量，以及排水管网的长度

	// 获取排水管网分页数据
	public Map<String, Object> getSearchDataMap(String typeName, PipeSearchParm pipeSearchParm, PageInfo pageInfo) throws Exception {
		ValidUtils.hasText(typeName, "管网类型为空,搜索失败");
		switch (typeName) {
		case "排水管网":
			return getPipelinesData(pipeSearchParm, pageInfo);
		case "污水处理厂":
			return getSewageData(pipeSearchParm, pageInfo);
		case "管网监测点":
			return getPipePoint(pipeSearchParm, pageInfo);
		case "规划管网":
			return getPipePlanData(pipeSearchParm, pageInfo);
		case "清源小区":
			return getCommunityData(pipeSearchParm, pageInfo);
		case "排水管网设施":
			return getPlumbingData(pipeSearchParm, pageInfo);
		case "内窥检测管网":
			return getEndoscopicDefectData(pipeSearchParm, pageInfo);
		default:
			throw new IllegalArgumentException("官网类型不存在");
		}
	}

	// 排水管网
	private Map<String, Object> getPipelinesData(PipeSearchParm pipeSearchParm, PageInfo pageInfo) throws Exception {
		String childTypeName = pipeSearchParm.getChildTypeName();
		String unitName = pipeSearchParm.getUnitName();
		String streetName = pipeSearchParm.getStreetName();
		String searchText = pipeSearchParm.getSearchText();

		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "SELECT t.*,ROWNUM as ROW_NO FROM SDE.PS_PIPE t WHERE 1 = 1";
		String lengthSql = "SELECT * FROM sde.ps_pipe t WHERE 1 = 1";

		if (!StringUtils.IsNullOrSpace(childTypeName)) {
			sql += " and t.pstype = @childTypeName";
			lengthSql += " and t.pstype = @childTypeName";
			parms.add(new GlobalParameter("childTypeName", childTypeName));
		}
		if (!StringUtils.IsNullOrSpace(streetName)) {
			sql += " and t.街道名称 = @streetName";
			lengthSql += " and t.街道名称 = @streetName";
			parms.add(new GlobalParameter("streetName", streetName));
		} else if (!StringUtils.IsNullOrSpace(unitName)) {
			sql += " and 行政区划 like @unitName";
			lengthSql += " and 行政区划 like @unitName";
			parms.add(new GlobalParameter("unitName", unitName + "%"));
		}

		if (!StringUtils.IsNullOrSpace(searchText)) {
			sql += " and (PRJ_NAME like @searchText  or 本点号 like @searchText)";
			lengthSql += " and (PRJ_NAME like @searchText  or 本点号 like @searchText)";
			parms.add(new GlobalParameter("searchText", "%" + searchText + "%"));
		}

		// 生成搜索条件字符串，并作为KEY缓存到Map中
		StringBuffer builder = new StringBuffer();
		builder.append(unitName);
		builder.append(streetName);
		builder.append(childTypeName);
		builder.append("排水管网");
		builder.append(searchText);
		String key = builder.toString();
		// 获取缓存
		Cache cache = CACHE_MAP.get(key);
		String totalLength = "0.0";
		PageInfo info;
		if (cache != null) {
			info = completePageInfo(cache.count, pageInfo);
			totalLength = cache.totalLength;
		} else {
			info = completePageInfo(sql, parms, pageInfo);
			lengthSql = "SELECT SUM(t.shape.len) TOTAL_LENGTH from (" + lengthSql + ")t WHERE 1 = 1";
			List<RowBase> rows = DataUtil.getSelectRows(lengthSql, parms);
			double length = rows.get(0).getSafeDouble("TOTAL_LENGTH");
			BigDecimal b = new BigDecimal(length);
			totalLength = String.valueOf(b.setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString());
			Cache newCache = new Cache(info.getCount(), totalLength);
			saveToCache(key, newCache);
		}

		// 开始查询
		// 增加分页条件--起始页和终点页
		parms.add(new GlobalParameter("startNum", caculteStartRowNum(pageInfo)));
		parms.add(new GlobalParameter("endNum", caculteEndRowNum(pageInfo)));
		sql += " and ROWNUM <= @endNum";

		String searchSql = " SELECT  t.objectid \"id\",t.街道名称 || ' ' || t.本点号 \"name\",'长度：' || trunc(t.shape.len, 2) || '米' \"des\",t.行政区划||t.路名 \"address\" FROM ("
				+ sql
				+ ")t where 1 = 1 and t.ROW_NO > @startNum";
		// 查询
		List<RowBase> searchResultRows = DataUtil.getSelectRows(searchSql, parms);
		Map<String, Object> resultMap = new HashMap<>();
		resultMap.put("pageInfo", info);
		resultMap.put("searchResult", searchResultRows);
		resultMap.put("extra", totalLength);
		return resultMap;
	}

	// 管网规划
	private Map<String, Object> getPipePlanData(PipeSearchParm pipeSearchParm, PageInfo pageInfo) throws Exception {
		// String childTypeName = pipeSearchParm.getChildTypeName();
		String unitName = pipeSearchParm.getUnitName();
		String streetName = pipeSearchParm.getStreetName();
		String searchText = pipeSearchParm.getSearchText();

		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "SELECT t.*,ROWNUM as ROW_NO FROM SDE.GHGW t WHERE 1 = 1";
		String lengthSql = "SELECT * FROM sde.GHGW t WHERE 1 = 1";

		if (!StringUtils.IsNullOrSpace(streetName)) {
			sql += " and t.jdname = @streetName";
			lengthSql += " and t.jdname = @streetName";
			parms.add(new GlobalParameter("streetName", streetName));
		} else if (!StringUtils.IsNullOrSpace(unitName)) {
			sql += " and t.ALLNAME like @unitName";
			lengthSql += " and t.ALLNAME like @unitName";
			parms.add(new GlobalParameter("unitName", unitName + "%"));
		}

		if (!StringUtils.IsNullOrSpace(searchText)) {
			sql += " and (t.ALLNAME like @searchText or t.OBJECTID like  @searchText)";
			lengthSql += " and (t.ALLNAME like @searchText or t.OBJECTID like  @searchText)";
			parms.add(new GlobalParameter("searchText", "%" + searchText + "%"));
		}

		// 生成搜索条件字符串，并作为KEY缓存到Map中
		StringBuffer builder = new StringBuffer();
		builder.append(unitName);
		builder.append(streetName);
		builder.append("管网规划");
		builder.append(searchText);
		String key = builder.toString();
		// 获取缓存
		Cache cache = CACHE_MAP.get(key);
		String totalLength = "0.0";
		PageInfo info;
		if (cache != null) {
			info = completePageInfo(cache.count, pageInfo);
			totalLength = cache.totalLength;
		} else {
			info = completePageInfo(sql, parms, pageInfo);
			lengthSql = "SELECT SUM(t.shape.len) TOTAL_LENGTH from (" + lengthSql + ")t WHERE 1 = 1";
			List<RowBase> rows = DataUtil.getSelectRows(lengthSql, parms);
			double length = rows.get(0).getSafeDouble("TOTAL_LENGTH");
			BigDecimal b = new BigDecimal(length);
			totalLength = String.valueOf(b.setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString());
			Cache newCache = new Cache(info.getCount(), totalLength);
			saveToCache(key, newCache);
		}

		// 开始查询
		parms.add(new GlobalParameter("startNum", caculteStartRowNum(pageInfo)));
		parms.add(new GlobalParameter("endNum", caculteEndRowNum(pageInfo)));
		sql += " and ROWNUM <= @endNum";
		String searchSql = "select t.Objectid \"id\",'编号：'|| t.objectid \"name\",'长度：' || trunc(t.shape.len, 2) || '米' \"des\",t.Allname \"address\" from ("
				+ sql
				+ ")t where 1 = 1 and t.ROW_NO > @startNum";

		// 查询
		List<RowBase> searchResultRows = DataUtil.getSelectRows(searchSql, parms);
		Map<String, Object> resultMap = new HashMap<>();
		resultMap.put("pageInfo", info);
		resultMap.put("searchResult", searchResultRows);
		resultMap.put("extra", totalLength);
		return resultMap;
	}

	// 管网设施
	private Map<String, Object> getPlumbingData(PipeSearchParm pipeSearchParm, PageInfo pageInfo) throws Exception {
		String childTypeName = pipeSearchParm.getChildTypeName();
		String unitName = pipeSearchParm.getUnitName();
		String streetName = pipeSearchParm.getStreetName();
		String searchText = pipeSearchParm.getSearchText();

		// 开始查询
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "SELECT t.*,ROWNUM as ROW_NO FROM SDE.PS_POINT t WHERE 1 = 1 ";

		if (!StringUtils.IsNullOrSpace(childTypeName)) {
			sql += " and t.POINT_TYPE = @childTypeName";
			parms.add(new GlobalParameter("childTypeName", "childTypeName"));
		}

		if (!StringUtils.IsNullOrSpace(streetName)) {
			sql += " and t.街道名称 = @streetName";
			parms.add(new GlobalParameter("streetName", streetName));
		} else if (!StringUtils.IsNullOrSpace(unitName)) {
			sql += " and t.行政区划 like @unitName";
			parms.add(new GlobalParameter("unitName", unitName + "%"));
		}
		if (!StringUtils.IsNullOrSpace(searchText)) {
			sql += " and (t.OBJECTID like @searchText or t.特征 like @searchText or t.PRJ_NAME like @searchText)";
			parms.add(new GlobalParameter("searchText", "%" + searchText + "%"));
		}

		// 生成搜索条件字符串，并作为KEY缓存到Map中
		StringBuffer builder = new StringBuffer();
		builder.append(unitName);
		builder.append(streetName);
		builder.append(childTypeName);
		builder.append("管网设施");
		builder.append(searchText);
		String key = builder.toString();
		// 获取缓存
		Cache cache = CACHE_MAP.get(key);
		PageInfo info;
		if (cache != null) {
			info = completePageInfo(cache.count, pageInfo);
		} else {
			info = completePageInfo(sql, parms, pageInfo);
			Cache newCache = new Cache(info.getCount(), "");
			saveToCache(key, newCache);
		}

		parms.add(new GlobalParameter("startNum", caculteStartRowNum(pageInfo)));
		parms.add(new GlobalParameter("endNum", caculteEndRowNum(pageInfo)));
		sql += " and ROWNUM <= @endNum";
		String searchSql = "select t.Objectid \"id\",t.街道名称 || ' ' || t.本点号 \"name\",'附属物：' || t.附属物 || ',特征：' || t.特征 || ',可见度：' || t.visibility \"des\",REPLACE(t.行政区划,' ','') || t.address \"address\" from ("
				+ sql
				+ ")t where 1 = 1 and t.ROW_NO > @startNum";

		// 查询
		List<RowBase> searchResultRows = DataUtil.getSelectRows(searchSql, parms);
		Map<String, Object> resultMap = new HashMap<>();
		resultMap.put("pageInfo", info);
		resultMap.put("searchResult", searchResultRows);
		return resultMap;
	}

	// 内窥缺陷
	private Map<String, Object> getEndoscopicDefectData(PipeSearchParm pipeSearchParm, PageInfo pageInfo) throws Exception {
		String childTypeName = pipeSearchParm.getChildTypeName();
		String unitName = pipeSearchParm.getUnitName();
		String streetName = pipeSearchParm.getStreetName();
		String searchText = pipeSearchParm.getSearchText();

		String childTypeId = null;
		if ("严重".equals(childTypeName)) {
			childTypeId = "4";
		} else if ("高".equals(childTypeName)) {
			childTypeId = "3";
		} else if ("中".equals(childTypeName)) {
			childTypeId = "2";
		} else if ("低".equals(childTypeName)) {
			childTypeId = "1";
		} else if ("无缺陷".equals(childTypeName)) {
			childTypeId = "0";
		}

		// 开始查询
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select t.*,t2.pipe_section_name,ROWNUM as ROW_NO\n"
				+ "  from sde.JCGW t\n"
				+ "  LEFT JOIN nkjc_defect t1\n"
				+ "    ON t.lno = t1.lno\n"
				+ "  left join PIPE_SECTION t2\n"
				+ "    on t1.pipe_no = t2.pipe_no\n"
				+ "  left join project_item t3\n"
				+ "    on t2.project_item_no = t3.item_no\n"
				+ "  left join PROJECT t4\n"
				+ "    on t3.project_no = t4.project_no\n"
				+ "  left join DATA_DEFULATDOMAIN t5\n"
				+ "    on t5.s_id = t3.DISTRICT\n"
				+ " WHERE t5.S_DOMAINNAME = '政区'\n"
				+ "   and length(t5.s_id) = '2'";
		String lengthSql = "select t.*\n"
				+ "  from sde.JCGW t\n"
				+ "  LEFT JOIN nkjc_defect t1\n"
				+ "    ON t.lno = t1.lno\n"
				+ "  left join PIPE_SECTION t2\n"
				+ "    on t1.pipe_no = t2.pipe_no\n"
				+ "  left join project_item t3\n"
				+ "    on t2.project_item_no = t3.item_no\n"
				+ "  left join PROJECT t4\n"
				+ "    on t3.project_no = t4.project_no\n"
				+ "  left join DATA_DEFULATDOMAIN t5\n"
				+ "    on t5.s_id = t3.DISTRICT\n"
				+ " WHERE t5.S_DOMAINNAME = '政区'\n"
				+ "   and length(t5.s_id) = '2'";
		if (!StringUtils.IsNullOrSpace(childTypeName)) {
			if ("0".equals(childTypeId)) {
				sql += " and (t.defect_level = '0' or t.defect_level is null) ";
				lengthSql += " and (t.defect_level = '0' or t.defect_level is null) ";
			} else {
				sql += " and t.defect_level = @childTypeId";
				lengthSql += " and t.defect_level = @childTypeId";
				parms.add(new GlobalParameter("childTypeId", childTypeId));
			}
		}
		if (!StringUtils.IsNullOrSpace(unitName)) {
			sql += " and t5.S_DOMAINCAPTION = @unitName";
			lengthSql += " and t5.S_DOMAINCAPTION = @unitName";
			parms.add(new GlobalParameter("unitName", unitName));
		}
		// if (!StringUtils.IsNullOrSpace(streetName)) {
		// sql += " and t5.S_DOMAINCAPTION like @streetName";
		// parms.add(new GlobalParameter("streetName", "%" + streetName + "%"));
		// }
		if (!StringUtils.IsNullOrSpace(searchText)) {
			sql += " and (t.ENAME like @searchText or t.LNO like @searchText)";
			parms.add(new GlobalParameter("searchText", "%" + searchText + "%"));
		}

		// 生成搜索条件字符串，并作为KEY缓存到Map中
		StringBuffer builder = new StringBuffer();
		builder.append(unitName);
		builder.append(streetName);
		builder.append(childTypeName);
		builder.append("污水处理厂");
		builder.append(searchText);
		String key = builder.toString();
		// 获取缓存
		Cache cache = CACHE_MAP.get(key);
		String totalLength = "0.0";
		PageInfo info;
		if (cache != null) {
			info = completePageInfo(cache.count, pageInfo);
			totalLength = cache.totalLength;
		} else {
			info = completePageInfo(sql, parms, pageInfo);
			lengthSql = "SELECT SUM(t.shape.len) TOTAL_LENGTH from (" + lengthSql + ")t WHERE 1 = 1";
			List<RowBase> rows = DataUtil.getSelectRows(lengthSql, parms);
			double length = rows.get(0).getSafeDouble("TOTAL_LENGTH");
			BigDecimal b = new BigDecimal(length);
			totalLength = String.valueOf(b.setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString());
			Cache newCache = new Cache(info.getCount(), totalLength);
			saveToCache(key, newCache);
		}

		// 开始查询
		// 增加分页条件--起始页和终点页
		parms.add(new GlobalParameter("startNum", caculteStartRowNum(pageInfo)));
		parms.add(new GlobalParameter("endNum", caculteEndRowNum(pageInfo)));
		sql += " and ROWNUM <= @endNum";

		String searchSql = "select t.objectid \"id\",\n"
				+ "       '缺陷编号：' || t.lno \"name\",\n"
				+ "       '长度：' || trunc(t.shape.len, 2) || '米' \"des\",\n"
				+ "        t.ename || '-' || t.pipe_section_name ||' 缺陷类型:'||t.defect_type \"address\"\n"
				+ "  from ( "
				+ sql
				+ ")t where 1 = 1 and t.ROW_NO > @startNum";

		// 查询
		List<RowBase> searchResultRows = DataUtil.getSelectRows(searchSql, parms);
		Map<String, Object> resultMap = new HashMap<>();
		resultMap.put("pageInfo", info);
		resultMap.put("searchResult", searchResultRows);
		resultMap.put("extra", totalLength);
		return resultMap;
	}

	// 管网点
	private Map<String, Object> getPipePoint(PipeSearchParm pipeSearchParm, PageInfo pageInfo) throws Exception {
		// String childTypeName = pipeSearchParm.getChildTypeName();
		// String unitName = pipeSearchParm.getUnitName();
		// String streetName = pipeSearchParm.getStreetName();
		// String searchText = pipeSearchParm.getSearchText();

		// 开始查询
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select t.objectid \"id\",\n"
				+ "       t.name \"name\",\n"
				+ "       '长度：' || trunc(t.shape.len, 2) || '米' \"des\",\n"
				+ "       '' \"address\"\n"
				+ "  from sde.monitor_station t\n"
				+ " where t.exp_no = 'WS050400500268'";
		List<RowBase> searchResultRows = DataUtil.getSelectRows(sql, parms);
		Map<String, Object> resultMap = new HashMap<>();
		resultMap.put("pageInfo", completePageInfo(searchResultRows.size(), pageInfo));
		resultMap.put("searchResult", searchResultRows);
		return resultMap;
	}

	// 清源小区
	private Map<String, Object> getCommunityData(PipeSearchParm pipeSearchParm, PageInfo pageInfo) throws Exception {
		String childTypeName = pipeSearchParm.getChildTypeName();
		String unitName = pipeSearchParm.getUnitName();
		String streetName = pipeSearchParm.getStreetName();
		String searchText = pipeSearchParm.getSearchText();

		// 开始查询
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "SELECT t.*,ROWNUM as ROW_NO FROM sde.building t where 1=1 ";
		String lengthSql = "SELECT * FROM sde.building t WHERE 1 = 1";
			
		if ("其它".equals(childTypeName)) {
			sql += " and t.状态  in ('其它','0')";		
			lengthSql+= " and t.状态  in ('其它','0')";		
		}else if (!StringUtils.IsNullOrSpace(childTypeName)) {
			sql += " and t.状态 = @childTypeName";
			lengthSql += " and t.状态 = @childTypeName";
			parms.add(new GlobalParameter("childTypeName", childTypeName));
		}
		
		if (!StringUtils.IsNullOrSpace(unitName)) {
			sql += " and t.district = @unitName";
			lengthSql += " and t.district = @unitName";
			parms.add(new GlobalParameter("unitName", unitName));
		}
		// if (!StringUtils.IsNullOrSpace(streetName)) {
		// sql += " and t.行政区划 like @streetName";
		// parms.add(new GlobalParameter("streetName", "%" + streetName + "%"));
		// }
		if (!StringUtils.IsNullOrSpace(searchText)) {
			sql += " and (t.OBJECTID like @searchText or t.小区名称 like @searchText)";
			lengthSql += " and (t.OBJECTID like @searchText or t.小区名称 like @searchText)";
			parms.add(new GlobalParameter("searchText", "%" + searchText + "%"));
		}

		// 生成搜索条件字符串，并作为KEY缓存到Map中
		StringBuffer builder = new StringBuffer();
		builder.append(unitName);
		builder.append(streetName);
		builder.append(childTypeName);
		builder.append("清源小区");
		builder.append(searchText);
		String key = builder.toString();
		// 获取缓存
		Cache cache = CACHE_MAP.get(key);
		String totalLength = "0.0";
		PageInfo info;
		if (cache != null) {
			info = completePageInfo(cache.count, pageInfo);
			totalLength = cache.totalLength;
		} else {
			info = completePageInfo(sql, parms, pageInfo);
			lengthSql = "SELECT SUM(t.shape.len) TOTAL_LENGTH from (" + lengthSql + ")t WHERE 1 = 1";
			List<RowBase> rows = DataUtil.getSelectRows(lengthSql, parms);
			double length = rows.get(0).getSafeDouble("TOTAL_LENGTH");
			BigDecimal b = new BigDecimal(length);
			totalLength = String.valueOf(b.setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString());
			Cache newCache = new Cache(info.getCount(), totalLength);
			saveToCache(key, newCache);
		}
		// 开始查询
		parms.add(new GlobalParameter("startNum", caculteStartRowNum(pageInfo)));
		parms.add(new GlobalParameter("endNum", caculteEndRowNum(pageInfo)));
		sql += " and ROWNUM <= @endNum";
		String searchSql = "select t.objectid \"id\",t.小区名称 \"name\",t.district || t.street || t.小区名称 || t.Household \"address\",'长度：' || trunc(t.shape.len, 2) || '米,状态：'||t.状态 \"des\" from ("
				+ sql
				+ ")t where 1 = 1 and t.ROW_NO > @startNum";

		// 查询
		List<RowBase> searchResultRows = DataUtil.getSelectRows(searchSql, parms);
		Map<String, Object> resultMap = new HashMap<>();
		resultMap.put("pageInfo", info);
		resultMap.put("searchResult", searchResultRows);
		resultMap.put("extra", totalLength);
		return resultMap;
	}

	// 污水处理厂
	private Map<String, Object> getSewageData(PipeSearchParm pipeSearchParm, PageInfo pageInfo) throws Exception {
		// String childTypeName = pipeSearchParm.getChildTypeName();
		String unitName = pipeSearchParm.getUnitName();
		String streetName = pipeSearchParm.getStreetName();
		String searchText = pipeSearchParm.getSearchText();
		String childTypeName = pipeSearchParm.getChildTypeName();
		String childTypeId = null;
		if ("国控".equals(childTypeName)) {
			childTypeId = "0";
		} else if ("省控".equals(childTypeName)) {
			childTypeId = "1";
		} else if ("市控".equals(childTypeName)) {
			childTypeId = "2";
		} else if ("其它".equals(childTypeName)) {
			childTypeId = "3";
		}

		// 开始查询
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "SELECT t.objectid,t1.name,t1.contact_address,t.shape.len len, ROWNUM as ROW_NO\n"
				+ "  FROM sde.ps_wwtpm t, BC_ENTERPRISE_ATTR t1\n"
				+ "  left join DATA_DEFULATDOMAIN t2\n"
				+ "    ON SUBSTR(t1.EXP_NO, 3, 2) = t2.s_id\n"
				+ " WHERE t1.exp_no = t.exp_no\n"
				+ "   and t2.S_DOMAINNAME = '政区'\n"
				+ "   and length(t2.s_id) = '2'";
		String lengthSql = "SELECT t.*\n"
				+ "  FROM sde.ps_wwtpm t, BC_ENTERPRISE_ATTR t1\n"
				+ "  left join DATA_DEFULATDOMAIN t2\n"
				+ "    ON SUBSTR(t1.EXP_NO, 3, 2) = t2.s_id\n"
				+ " WHERE t1.exp_no = t.exp_no\n"
				+ "   and t2.S_DOMAINNAME = '政区'\n"
				+ "   and length(t2.s_id) = '2'";

		if (!StringUtils.IsNullOrSpace(childTypeName)) {
			sql += " and t1.attention_level = @childTypeId";
			lengthSql += " and t1.attention_level = @childTypeId";
			parms.add(new GlobalParameter("childTypeId", childTypeId));
		}

		if (!StringUtils.IsNullOrSpace(streetName)) {
			sql += " and t.街道名称 = @streetName";
			lengthSql += " and t.街道名称 = @streetName";
			parms.add(new GlobalParameter("streetName", streetName));
		} else if (!StringUtils.IsNullOrSpace(unitName)) {
			sql += " and t2.s_domaincaption = @unitName";
			lengthSql += " and t2.s_domaincaption = @unitName";
			parms.add(new GlobalParameter("unitName", unitName));
		}
		if (!StringUtils.IsNullOrSpace(searchText)) {
			sql += " and (t.OBJECTID like @searchText or t1.NAME like @searchText)";
			lengthSql += " and (t.OBJECTID like @searchText or t1.NAME like @searchText)";
			parms.add(new GlobalParameter("searchText", "%" + searchText + "%"));
		}
		// 生成搜索条件字符串，并作为KEY缓存到Map中
		StringBuffer builder = new StringBuffer();
		builder.append(unitName);
		builder.append(streetName);
		builder.append(childTypeName);
		builder.append("污水处理厂");
		builder.append(searchText);
		String key = builder.toString();
		// 获取缓存
		Cache cache = CACHE_MAP.get(key);
		String totalLength = "0.0";
		PageInfo info;
		if (cache != null) {
			info = completePageInfo(cache.count, pageInfo);
			totalLength = cache.totalLength;
		} else {
			info = completePageInfo(sql, parms, pageInfo);
			lengthSql = "SELECT SUM(t.shape.len) TOTAL_LENGTH from (" + lengthSql + ")t WHERE 1 = 1";
			List<RowBase> rows = DataUtil.getSelectRows(lengthSql, parms);
			double length = rows.get(0).getSafeDouble("TOTAL_LENGTH");
			BigDecimal b = new BigDecimal(length);
			totalLength = String.valueOf(b.setScale(2, BigDecimal.ROUND_HALF_UP).toPlainString());
			Cache newCache = new Cache(info.getCount(), totalLength);
			saveToCache(key, newCache);
		}

		// 开始查询
		parms.add(new GlobalParameter("startNum", caculteStartRowNum(pageInfo)));
		parms.add(new GlobalParameter("endNum", caculteEndRowNum(pageInfo)));
		sql += " and ROWNUM <= @endNum";
		String searchSql = "select t.objectid \"id\",\n"
				+ "       t.name \"name\",\n"
				+ "       t.contact_address \"address\",\n"
				+ "       '长度：' || trunc(t.len, 2) ||'米' \"des\" from ("
				+ sql
				+ ")t where 1 = 1 and t.ROW_NO > @startNum";
		// 查询
		List<RowBase> searchResultRows = DataUtil.getSelectRows(searchSql, parms);
		Map<String, Object> resultMap = new HashMap<>();
		resultMap.put("pageInfo", info);
		resultMap.put("searchResult", searchResultRows);
		resultMap.put("extra", totalLength);
		return resultMap;
	}

	private PageInfo completePageInfo(String sql, List<GlobalParameter> parms, PageInfo pageInfo) throws Exception {
		int count = SqlUtils.getRowsCount(sql, parms);
		int pageCount = (count + pageInfo.getPageSize() - 1) / pageInfo.getPageSize();
		pageInfo.setCount(count);
		pageInfo.setPageCount(pageCount);
		return pageInfo;
	}

	private PageInfo completePageInfo(int count, PageInfo pageInfo) throws Exception {
		int pageCount = (count + pageInfo.getPageSize() - 1) / pageInfo.getPageSize();
		pageInfo.setCount(count);
		pageInfo.setPageCount(pageCount);
		return pageInfo;
	}

	private int caculteStartRowNum(PageInfo pageInfo) throws Exception {
		ValidUtils.notNull(pageInfo);
		return (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize();
	}

	private int caculteEndRowNum(PageInfo pageInfo) throws Exception {
		ValidUtils.notNull(pageInfo);
		return pageInfo.getPageIndex() * pageInfo.getPageSize();
	}

	private void saveToCache(String key, Cache cache) {
		if (CACHE_MAP.size() > 100_0000) {
			CACHE_MAP.clear();
		}
		CACHE_MAP.put(key, cache);
	}

	private static class Cache {
		private int count;
		private String totalLength;

		public Cache(int count, String totalLength) {
			this.count = count;
			this.totalLength = totalLength;
		}
	}
}
